﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace DAL
{
    public class SinhVien
    {
        //private const String SConn = "Data Source=DUCLINH-PC;Initial Catalog=HOUSMS;Persist Security Info=True;User ID=sa;Password=minhduc";
        private static string SConn = ConfigurationManager.ConnectionStrings["DucLM"].ConnectionString;
        #region "Lấy dữ liệu về sinh viên để đưa ra datagridview"
        public DataSet Binding()
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("GetAllSinhVien", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.Fill(ds, "LoadData");
            return ds;
        }
        #endregion

        #region "Lấy sinh viên theo lớp để điểm danh"
        public DataSet GetSVDiemDanh(string tenLop)
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("GetSinhVienDiemDanh", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@TenLop", tenLop);
            da.Fill(ds, "GetSVDiemDanh");
            return ds;
        }
        #endregion

        #region "Xóa sinh viên"
        public static int Delete(String maSV)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = new SqlCommand("DeleteSinhVien", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Parameters.AddWithValue("@MaSV", maSV);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                cmd.Dispose();
            }
        }
        #endregion

        #region "Thêm mới sinh viên"
        public int Add(String MaSV, String TenSV, String TenLop, DateTime ngaySinh, String noisinh, byte[] anhSV)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = new SqlCommand("AddSinhVien", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Parameters.AddWithValue("@MaSV", MaSV);
                cmd.Parameters.AddWithValue("@TenSV", TenSV);
                cmd.Parameters.AddWithValue("@TenLop", TenLop);
                cmd.Parameters.AddWithValue("@NgaySinh", ngaySinh);
                cmd.Parameters.AddWithValue("@NoiSinh", noisinh);
                cmd.Parameters.AddWithValue("@AnhSV", anhSV);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Sửa thông tin sinh viên"
        public  int Update(string MaSV, string TenLop)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = new SqlCommand("UpdateSinhVien", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Parameters.AddWithValue("@MaSV", MaSV);
                cmd.Parameters.AddWithValue("@TenLop", TenLop);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Kiểm tra xem mã sinh viên đã tồn tại hay chưa"
        public static DataTable  CheckMaSV(string MaSV)
        {
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("CheckMaSV", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@MaSV",  MaSV);
            var ds = new DataSet();
            da.Fill(ds, "CheckMa");
            DataTable dt = ds.Tables["CheckMa"];
            return dt;
        }
        #endregion

        #region "Tìm kiếm theo mã sinh viên"
        public DataSet SearchMaSV(string MaSV)
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("SearchByMaSV", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@MaSV", MaSV);
            da.Fill(ds, "SearchMaSV");
            return ds;
        }
        #endregion

        #region "Tìm kiếm theo tên sinh viên"
        public DataSet SearchTenSV(string TenSV)
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("SearchTenSV", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@TenSV", TenSV);
            da.Fill(ds, "SearchTenSV");
            return ds;
        }
        #endregion

        #region "Lấy sinh viên theo lớp"
        public DataSet GetSVByLop(string tenLop)
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("GetSVByLop", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@TenLop", tenLop);
            da.Fill(ds, "GetSVByLop");
            return ds;
        }
        #endregion

        #region "Load điểm sinh viên"
        public DataSet GetDiemSV(int maMonHoc, string tenLop)
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("GetKq", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@MaMonHoc", maMonHoc);
            da.SelectCommand.Parameters.AddWithValue("@TenLop", tenLop);
            da.Fill(ds, "GetKq");
            return ds;
        }
        #endregion

        #region "Lưu điểm"
        public int InsertKq(string maSV, int ky, int maMonHoc, string tenLop, int lanThi, int diemThi, int diemThanhPhan, string diemTongKet)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = new SqlCommand("InsertKq", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Parameters.AddWithValue("@MaSV", maSV);
                cmd.Parameters.AddWithValue("@Ky", ky);
                cmd.Parameters.AddWithValue("@MaMonHoc", maMonHoc);
                cmd.Parameters.AddWithValue("@TenLop", tenLop);
                cmd.Parameters.AddWithValue("@LanThi", lanThi);
                cmd.Parameters.AddWithValue("@DiemThi", diemThi);
                cmd.Parameters.AddWithValue("@DiemThanhPhan", diemThanhPhan);
                cmd.Parameters.AddWithValue("@DiemTongKet", diemTongKet);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Lấy mã sinh viên từ tên sinh viên trong datagridview"
        public DataSet GetMaSV(string tenSV)
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("GetMaSV", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@TenSV", tenSV);
            da.Fill(ds, "GetMaSV");
            return ds;
        }
        #endregion
    }
}
